In order to trim spaces BigQuery provides the following functions: TRIM, LTRIM and RTRIM which removes spaces from the start and/or the end of any string.

WITH items AS
  (SELECT "   apple   " as item
  UNION ALL
  SELECT "   banana   " as item
  UNION ALL
  SELECT "   orange   " as item)
SELECT
  item as original_value,
  CONCAT("#", TRIM(item), "#") as TRIM_example,
  CONCAT("#", LTRIM(item), "#") as LTRIM_example,
  CONCAT("#", RTRIM(item), "#") as RTRIM_example,
FROM items;

`original_value`	`TRIM_exampl`	`LTRIM_example`	`RTRIM_example`
`apple`	`#apple#`	`#apple #`	`# apple#`
`apple`	`#apple#`	`#apple #`	`# apple#`
`apple`	`#apple#`	`#apple #`	`# apple#`